package com.njzxw.fs.commonutil.web;

import com.njzxw.fs.commonutil.bean.*;
import com.njzxw.fs.commonutil.interfaces.MsmInterface;
import com.njzxw.fs.commonutil.interfaces.OssUtilInterface;
import com.njzxw.fs.commonutil.repository.BaseRep;
import com.njzxw.fs.commonutil.repository.ResouceReq;
import com.njzxw.fs.commonutil.util.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.NotOfficeXmlFileException;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.io.InputStream;
import java.net.ConnectException;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;
import java.util.*;

public class BaseServer {

    public org.slf4j.Logger logger = LoggerFactory.getLogger(this.getClass());

    /**
     * 返回参数设定
     */
    @Autowired
    protected ReturnMap returnMap;

    @Resource(name = "localOssImp")
    protected OssUtilInterface ossUtil;

    @Autowired
    protected RedisUtil redisUtil;

    @Autowired
    protected StringHelper stringHelper;

    @Autowired
    protected RequestHelper requestHelper;

    @Autowired
    protected Common common;

    @Resource(name = "requestHelper")
    protected RequestHelper req;

    @Autowired
    protected FtpUtils ftpUtils;

    @Autowired
    protected Validator validator;

    @Autowired
    protected DownloadImage downloadImage;

    @Autowired
    protected VerifyCodeUtils verifyCodeUtils;

    @Autowired
    protected UserUtil userUtil;

    @Autowired
    protected ExcelUtils excelUtils;

    @Autowired
    protected ResouceReq resouceReq;

    @Resource(name="aliyunMsmImp")
    protected MsmInterface msmInterface;

    public ReturnMap importFile(String fileDir, String importTable, List<String> verifySql, List<String> insertSql, List<Map<String,String>> verifyList, BaseBean baseBean){

        return importFile(fileDir,importTable,verifySql,insertSql,verifyList,baseBean,null);
    }

    /**
     *
     * 批量导入操作(不要在调用该方法的父级方法设置@Transactional，否则会报错)
     *
     * @param fileDir 导入文件保存到那个目录，如："/user/import/"
     * @param importTable  导入的主表是那个？如：t_manager_user
     * @param verifySql 校验正确性的语句，临时表明使用#tmptable代替，其他语句正常操作
     * @param insertSql 插入数据的语句，第一条请直接插入到 importTable设置的表中，否则会存在问题，临时表明使用#tmptable代替，其他语句正常操作
     * @param verifyList 此字段设置在读取xls文件时就进行正则表达式验证数据正确性 没有传入null
     * @param baseBean  Resouce_id必须存在值
     * @return
     */
    public ReturnMap importFile(String fileDir, String importTable, List<String> verifySql, List<String> insertSql, List<Map<String,String>> verifyList, BaseBean baseBean,ExcelUtils.ImportTableExecuteInterface importTableExecuteInterface){
        String file_id = ossUtil.exeFile(baseBean.getResouce_id(),fileDir);

        if(StringUtils.isBlank(file_id)){
            return returnMap.getErrorReturnMap("文件处理失败，未查找到对应上传文件，请重新上传。");
        }

        String table = importTable;

        String tableName = "tmp_import_"+(new Random().nextInt(100000));

        String path = resouceReq.getPath(file_id);
//        logger.debug("path:"+path);
        if(StringUtils.isBlank(path)){
            return returnMap.getErrorReturnMap("文件可能丢失，未查找到对应上传文件，请重新上传。");
        }

        logger.debug("path::"+path);

        //下载文件
        // 构造URL
        URL url = null;
        InputStream is = null;
        try {
            url = new URL(path);
            // 打开连接
            URLConnection con = url.openConnection();
            //设置请求超时为5s
            con.setConnectTimeout(10 * 1000);
            // 输入流
            is = con.getInputStream();

            List<Map<String,Object>> list = excelUtils.readExcelMap(is,verifyList);
//            logger.debug("list:"+list);
            int listNum = list.size();

            int autoNum = resouceReq.getAutoNum(table);

            //进行临时表sql拼接
            if(list != null && !list.isEmpty()){
                int cnt = list.get(0).size()-1;

                String createTbaleSql = "create table "+tableName +"(";

                for(int i=0;i<cnt;i++){
                    createTbaleSql += "`col"+i+"` varchar(200) COLLATE utf8_bin DEFAULT NULL,";
                }
                createTbaleSql += "`id` bigint(20) NOT NULL AUTO_INCREMENT,";
                createTbaleSql += "`state` varchar(200) COLLATE utf8_bin DEFAULT NULL,";
                createTbaleSql += "`errorMsg` varchar(200) COLLATE utf8_bin DEFAULT NULL,";
                createTbaleSql += "PRIMARY KEY (`id`) USING BTREE)ENGINE=InnoDB AUTO_INCREMENT="+autoNum+" DEFAULT CHARSET=utf8 COLLATE=utf8_bin";
                resouceReq.createTmpTable(createTbaleSql);

                resouceReq.updateTableAUtoNum(tableName,autoNum+list.size()+1);

                //平均插入语句
                String insertSQL = "INSERT INTO "+tableName+"(";
                for(int i=0;i<cnt;i++){
                    insertSQL += "`col"+i+"`,";
                    if(i == cnt -1){
                        insertSQL += "`errorMsg`";
                    }
                }
                insertSQL += ")values";

                for(int i=1;i<listNum;i++){
                    Map<String,Object> map_ = list.get(i);
                    insertSQL += "(";
                    for(int j=0;j<cnt;j++){
                        String val1 = StringHelper.get(map_,"col"+j);
                        insertSQL += (StringUtils.isBlank(val1)?"null":"'"+val1+"'")+",";
                        if(j == cnt -1){
                            String val = StringHelper.get(map_,"errorMsg");
                            insertSQL += (StringUtils.isBlank(val)?"null":"'"+val+"'");
                        }
                    }
                    if(i == listNum-1){
                        insertSQL += ")";
                    }else{
                        insertSQL += "),";
                    }
                }
                //插入到临时表中
                Integer sue = resouceReq.insertInto(insertSQL);

                if(sue < 0){
                    return returnMap.getErrorReturnMap("数据新增操作错误");
                }

                return excelUtils.importTableList(list,verifySql,insertSql,tableName,importTableExecuteInterface);

            }else{
                return returnMap.getErrorReturnMap("未检测到文件中存在数据");
            }

        }catch (ConnectException conE){
            conE.printStackTrace();
            return returnMap.getErrorReturnMap("网络可能不顺畅，请重新尝试。");
        }catch (NotOfficeXmlFileException noFileE){
            noFileE.printStackTrace();
            return returnMap.getErrorReturnMap("您上传的文件可能不是标准的office文件");
        }catch (Exception e) {
            e.printStackTrace();
        }

        return returnMap.getErrorReturnMap("未知错误，请稍后再行尝试");
    }

    /**
     * 单文件，转换bean中Resouce_id对应ID到真实路径 重新放到Resouce_id变量中,如果操作失败，Resouce_id将会变空值
     * @param baseBean
     * @param tableName 无用处
     * @param dir
     * @return
     */
    public void exeResouce(BaseBean baseBean,String tableName,String dir){
        baseBean.setResouce_id(exeResouce(tableName,dir,baseBean.getResouce_id()));
    }

    /**
     * 单文件，转换bean中Resouce_id对应ID到真实路径 重新放到Resouce_id变量中,如果操作失败，Resouce_id将会变空值
     * @param tableName 无用处
     * @param dir
     * @param pathid 对应临时文件ID
     * @return
     */
    public String exeResouce(String tableName,String dir,String pathid){
        if(StringUtils.isBlank(pathid)){
            return null;
        }
        if(!StringUtils.isBlank(pathid)&&!pathid.startsWith("y")&&!pathid.startsWith("z")){
            return ossUtil.exeFile(pathid,dir);
        }else if(pathid.startsWith("y")){//已经存在
            return pathid.replace("y","");
        }else if(pathid.startsWith("z")){//正式文件存在
            return pathid.replace("z","");
        }
        return null;
    }

    /**
     * 单文件，转换bean中Resouce_id对应ID到真实路径 重新放到Resouce_id变量中,如果操作失败，Resouce_id将会变空值
     * @param tableName 无用处
     * @param dir
     * @param pathid 对应临时文件ID
     * @param oper_id 操作人ID
     * @param oper_type 1后台人员 2前端
     * @param oper_name 操作人名称
     * @return
     */
    public String exeResouce(String tableName,String dir,String pathid,String oper_id,int oper_type,String oper_name){
        if(StringUtils.isBlank(pathid)){
            return null;
        }
        if(!StringUtils.isBlank(pathid)&&!pathid.startsWith("y")&&!pathid.startsWith("z")){
            return ossUtil.exeFile(pathid,dir,oper_id,oper_type,oper_name);
        }else if(pathid.startsWith("y")){//已经存在
            return pathid.replace("y","");
        }else if(pathid.startsWith("z")){//正式文件存在
            return pathid.replace("z","");
        }
        return null;
    }

    /**
     * 进行多文件处理,操作成功返回null
     *
     * @param baseBean
     * @param tableName
     * @param col_name
     * @param dir
     * @return
     */
    public ReturnMap exeResouces(BaseBean baseBean,String tableName,String col_name,String dir){
        return exeResouces(baseBean.getResouce_ids(),baseBean.getId(),tableName,col_name,dir);
    }

    /**
     *
     * @param pathsList  资源ID列表
     * @param id   操作表ID
     * @param tableName 操作表名
     * @param col_name 操作表列
     * @param dir 存储路径
     * @return
     */
    public ReturnMap exeResouces(List<String> pathsList,String id,String tableName,String col_name,String dir){
        if(pathsList != null && !pathsList.isEmpty()){
            String sql = "delete from "+tableName+" where "+col_name+" = "+id;
            //先进行统一删除操作
            if(resouceReq.delete(sql) < 0){
                return returnMap.getErrorReturnMap("文件操作失败");
            }
            List<String> ids = new ArrayList<>();
            for(int i=0;i<pathsList.size();i++){
                String pathId = pathsList.get(i);
                if(pathId.startsWith("y")){//代表是已经存在的关系，直接插入即可不用走图片处理
                    sql = "insert into "+tableName+"(resouce_id,"+col_name+")values("+(pathId.replace("y",""))+","+id+")";
                    if(resouceReq.insert(sql) <= 0){
                        return returnMap.getErrorReturnMap("处理失败，请重新上传");
                    }
                }else{
                    ids.add(pathId);
                }
            }

            if(ids.size() != 0){
                List<String> resultList = ossUtil.exeFile(ids,dir);
                logger.debug("resultList:",resultList);
                if(resultList != null && resultList.size() != ids.size()){
                    return returnMap.getErrorReturnMap("处理失败，请重新上传");
                }
                for(int i=0;i<resultList.size();i++){
                    sql = "insert into "+tableName+"(resouce_id,"+col_name+")values("+resultList.get(i)+","+id+")";
                    if(resouceReq.insert(sql) <= 0){
                        return returnMap.getErrorReturnMap("处理失败，请重新上传");
                    }
                }


            }

        }

        return null;
    }

    /**
     * 处理列表中的图片路径信息
     *
     * @param list
     * @param pathName
     * @param resouceIdName
     * @param listPathName
     */
    public void executeListPath(List<Map<String,Object>> list,String pathName,String resouceIdName,String listPathName){
        for(int i=0;i<list.size();i++){
            Map<String,Object> map = list.get(i);
            executeMapPath(map,pathName,resouceIdName,listPathName);
        }
    }

    /**
     * 处理详细信息中的图片路径信息
     *
     * @param map
     * @param pathName
     * @param resouceIdName
     * @param listPathName
     */
    public void executeMapPath(Map<String,Object> map,String pathName,String resouceIdName,String listPathName){

        String paths = StringHelper.get(map,pathName);
        String resouce_ids = StringHelper.get(map,resouceIdName);

        List<Map<String,Object>> list_ = new ArrayList<>();
        if(!StringUtils.isBlank(paths)){
            String[] path_ = paths.split(",");
            String[] resouce_ids_ = resouce_ids.split(",");
            for(int j=0;j<path_.length;j++){
                Map<String,Object> map_ = new HashMap<>();
                map_.put("id",resouce_ids_[j]);
                map_.put("path",path_[j]);
                list_.add(map_);
            }
        }
//        logger.debug("list_",list_);
//        logger.debug("listPathName",listPathName);
        map.put(listPathName,list_);
    }


}
